﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace _4DJackPot
{
    public class DbInitMSACCESS
    {
        public DbInitMSACCESS()
        {

        }

        public bool ExecInit()
        {
            if (!ExistTableCheck("USER_TB"))
            {
                if (!USER_TBInit()) return false;
            }

            if (!ExistTableCheck("bets"))
            {
                if (!bets_TBInit()) return false;
            }
            if (!ExistTableCheck("bet_winnings"))
            {
                if (!bet_winnings_TBInit()) return false;
            }
            if (!ExistTableCheck("companies"))
            {
                if (!companies_TBInit()) return false;
            }
            if (!ExistTableCheck("company_games"))
            {
                if (!company_games_TBInit()) return false;
            }
            if (!ExistTableCheck("game_split"))
            {
                if (!game_split_TBInit()) return false;
            }
            if (!ExistTableCheck("game_winning_numbers"))
            {
                if (!game_winning_numbers_TBInit()) return false;
            }
            if (!ExistTableCheck("games"))
            {
                if (!games_TBInit()) return false;
            }
            if (!ExistTableCheck("permissions"))
            {
                if (!permissions_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_default_type_amount"))
            {
                if (!prize_default_type_amount_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_default_types"))
            {
                if (!prize_default_types_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_type_amount"))
            {
                if (!prize_type_amount_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_types"))
            {
                if (!prize_types_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_types_default_match"))
            {
                if (!prize_types_default_match_TBInit()) return false;
            }
            if (!ExistTableCheck("users"))
            {
                if (!users_TBInit()) return false;
            }


            return true;
        }

        // users
        public bool users_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE users (" +
                        "[user_id] VARCHAR NOT NULL, " +
                        "[password] VARCHAR, " +
                        "[name] VARCHAR, " +
                        "[permission_id] INTEGER, " +
                        "[registered_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_users] PRIMARY KEY " +
                        "( " +
                            "[user_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_types_default_match
        public bool prize_types_default_match_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_types_default_match (" +
                        "[prize_type_id] INTEGER NOT NULL, " +
                        "[prize_default_type_id] INTEGER, " +
                        "CONSTRAINT [PK_prize_types_default_match] PRIMARY KEY " +
                        "( " +
                            "[prize_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_type_amount
        public bool prize_types_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_types (" +
                        "[prize_type_id] INTEGER NOT NULL, " +
                        "[prize_name] VARCHAR, " +
                        "CONSTRAINT [PK_prize_types] PRIMARY KEY " +
                        "( " +
                            "[prize_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_type_amount
        public bool prize_type_amount_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_type_amount (" +
                        "[prize_type_id] INTEGER NOT NULL, " +
                        "[forecast] VARCHAR, " +
                        "[amount] INTEGER, " +
                        "CONSTRAINT [PK_prize_type_amount] PRIMARY KEY " +
                        "( " +
                            "[prize_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_default_types
        public bool prize_default_types_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_default_types (" +
                        "[prize_default_type_id] INTEGER NOT NULL, " +
                        "[prize_default_name] VARCHAR, " +
                        "CONSTRAINT [PK_prize_default_types] PRIMARY KEY " +
                        "( " +
                            "[prize_default_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_default_type_amount
        public bool prize_default_type_amount_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_default_type_amount (" +
                        "[prize_default_type_id] INTEGER NOT NULL, " +
                        "[default_forecast] VARCHAR, " +
                        "[default_amount] INTEGER, " +
                        "CONSTRAINT [PK_prize_default_type_amount] PRIMARY KEY " +
                        "( " +
                            "[prize_default_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // permissions
        public bool permissions_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE permissions (" +
                        "[permission_id] INTEGER NOT NULL, " +
                        "[permission_name] VARCHAR, " +
                        "[draw_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_permissions] PRIMARY KEY " +
                        "( " +
                            "[permission_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // games
        public bool games_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE games (" +
                        "[game_id] INTEGER NOT NULL, " +
                        "[created_datetime] VARCHAR, " +
                        "[draw_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_games] PRIMARY KEY " +
                        "( " +
                            "[game_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // game_winning_numbers
        public bool game_winning_numbers_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE game_winning_numbers (" +
                        "[game_id] INTEGER NOT NULL, " +
                        "[prize_type_id] INTEGER, " +
                        "[order] INTEGER, " +
                        "[number] INTEGER, " +
                        "CONSTRAINT [PK_game_winning_numbers] PRIMARY KEY " +
                        "( " +
                            "[game_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // game_split
        public bool game_split_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE game_split (" +
                        "[company_id] INTEGER NOT NULL, " +
                        "[ratio] DOUBLE, " +
                        "CONSTRAINT [PK_game_split] PRIMARY KEY " +
                        "( " +
                            "[company_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // company_games
        public bool company_games_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE company_games (" +
                        "[company_id] INTEGER NOT NULL, " +
                        "[game_id] INTEGER, " +
                        "CONSTRAINT [PK_company_games] PRIMARY KEY " +
                        "( " +
                            "[company_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // companies
        public bool companies_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE companies (" +
                        "[company_id] INTEGER NOT NULL, " +
                        "[company_name] VARCHAR, " +
                        "[company_logo_url] VARCHAR, " +
                        "CONSTRAINT [PK_companies] PRIMARY KEY " +
                        "( " +
                            "[company_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // bets
        public bool bets_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE bets (" +
                        "[bet_id] INTEGER NOT NULL, " +
                        "[game_id] INTEGER, " +
                        "[user_id] VARCHAR, " +
                        "[bet_number] DOUBLE, " +
                        "[bet_amount] INTEGER, " +
                        "[bet_datetime] VARCHAR, " +
                        "[forecast] VARCHAR, " +
                        "[permitation] VARCHAR, " +
                        "CONSTRAINT [PK_bets] PRIMARY KEY " +
                        "( " +
                            "[bet_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // bet_winnings
        public bool bet_winnings_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE bet_winnings (" +
                        "[bet_id] INTEGER NOT NULL, " +
                        "[prize_type_id] INTEGER, " +
                        "[bet_winning_amount] INTEGER, " +
                        "[receipt_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_bet_winnings] PRIMARY KEY " +
                        "( " +
                            "[bet_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        public bool ExistTableCheck(string tableNameString)
        {
            DataTable dt = Global.dBCon.GetSchema("tables");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string dbTableNameString = dt.Rows[i]["TABLE_NAME"].ToString();
                if (dbTableNameString == tableNameString)
                {
                    return true;
                }
            }

            return false;
        }

        // USER_TB
        public bool USER_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE USER_TB (" +
                        "[USER_SABUN] VARCHAR NOT NULL, " +
                        "[USER_NAME] VARCHAR, " +
                        "[USER_BUSER] VARCHAR, " +
                        "[USER_PWD] VARCHAR, " +
                        "[USER_LEVEL] VARCHAR, " +
                        "[USER_INDATE] VARCHAR, " +
                        "[USER_UPDATE] VARCHAR, " +
                        "CONSTRAINT [PK_USER_TB] PRIMARY KEY " +
                        "( " +
                            "[USER_SABUN] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();

                string currentDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                string insertQueryString =
                    "INSERT INTO USER_TB(USER_SABUN, USER_NAME, USER_BUSER, USER_PWD, USER_LEVEL, USER_INDATE, USER_UPDATE) " +
                    "VALUES('99999', '시스템관리자', '넥솔시스템', '99999', '99999', '" + currentDate + "', '" + currentDate + "')";
                Global.dBCmd.CommandText = insertQueryString;
                Global.dBCmd.ExecuteNonQuery();
                insertQueryString =
                    "INSERT INTO USER_TB(USER_SABUN, USER_NAME, USER_BUSER, USER_PWD, USER_LEVEL, USER_INDATE, USER_UPDATE) " +
                    "VALUES('1', 'ADMINISTRATOR', 'ADMIN', '1', '1', '" + currentDate + "', '" + currentDate + "') ";
                Global.dBCmd.CommandText = insertQueryString;
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

    }
}
